/*
 * 文件名：ReaderService.java.
 * 描述：
 * 创建人: 龚洪亮
 * 创建时间：2017年8月11日
 */

package com.fiberhome.platform.service;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.fiberhome.platform.common.PageResult;
import com.fiberhome.platform.mapper.DesignNeedMapper;
import com.fiberhome.platform.mapper.NeedCategoryMapper;
import com.fiberhome.platform.mapper.TestCaseMapper;
import com.fiberhome.platform.mapper.TestCategoryMapper;
import com.fiberhome.platform.pojo.DesignNeed;
import com.fiberhome.platform.pojo.NeedCategory;
import com.fiberhome.platform.pojo.NeedCategoryExample;
import com.fiberhome.platform.pojo.TestCase;
import com.fiberhome.platform.pojo.TestCategory;

/**
 * @Description
 * @author 龚洪亮
 * @version 2017年8月11日
 */
@Service
public class ReaderService {
	@Autowired
	private NeedCategoryMapper needCategoryMapper;
	@Autowired
	private DesignNeedMapper designNeedMapper;
	@Autowired
	private TestCategoryMapper testCategoryMapper;
	@Autowired
	private TestCaseMapper testCaseMapper;

	/**
	 * @Description
	 * @Date 2017年8月11日
	 * @return
	 * @throws IOException
	 */
	@Transactional
	public PageResult readNeedExcel(String path) {
		try {

			designNeedMapper.clear();
			needCategoryMapper.clear();

			InputStream is = new FileInputStream(path);
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				System.out.println("number of sheet:" + numSheet);
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}

				for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					System.out.println("number of rows:" + rowNum);
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					// 开始读取行
					boolean flag = true;
					int id_pre = 0;
					String num = "";
					System.out.println(hssfRow.getCell(5).getStringCellValue().trim());
					if (hssfRow.getCell(5).getStringCellValue().trim().equals("PON功能库")) {

						for (int colIx = 4; colIx <= 11 && flag; colIx++) {

							if (colIx == 4) {
								HSSFCell cell = hssfRow.getCell(colIx);
								String cellString = "";

								switch (cell.getCellType()) {

								case Cell.CELL_TYPE_STRING:
									cellString = cell.getStringCellValue();

									num = cellString;
									break;
								default:
									break;
								}
							} else {

								HSSFCell cell = hssfRow.getCell(colIx);
								if (cell == null) {
									NeedCategory need = new NeedCategory();
									need.setId(id_pre);
									need.setIsParent(0);
									need.setDesignNo(num);
									needCategoryMapper
											.updateByPrimaryKeySelective(need);
									id_pre = 0;
									break;
								}
								String cellString = "";

								switch (cell.getCellType()) {

								case Cell.CELL_TYPE_STRING:
									cellString = cell.getStringCellValue()
											.trim();

									// 首先判断是否已经存过了，如果已经存过了，就返回存过的单元格的id，如果没有存过，就返回的是-1
									NeedCategory near = getNearNeedCat(cellString);

									if (near == null) {
										NeedCategory need = new NeedCategory();
										need.setParentId(id_pre);
										need.setName(cellString);
										need.setStatus(1);
										need.setSortOrder(1);
										need.setIsParent(1);
										need.setCreated(new Date());
										need.setUpdated(new Date());
										need.setType(0);
										need.setIsPon(1);
										needCategoryMapper.insert(need);
										cell.setCellValue(need.getId() + "");
										id_pre = need.getId();
									} else {
										if (near.getParentId() == id_pre) {
											cell.setCellValue(near.getId() + "");
											id_pre = near.getId();
										} else {
											NeedCategory need = new NeedCategory();
											need.setParentId(id_pre);
											need.setName(cellString);
											need.setStatus(1);
											need.setSortOrder(1);
											need.setIsParent(1);
											need.setCreated(new Date());
											need.setUpdated(new Date());
											need.setType(0);
											need.setIsPon(1);
											needCategoryMapper.insert(need);
											cell.setCellValue(need.getId() + "");
											id_pre = need.getId();
										}

									}
									break;
								case Cell.CELL_TYPE_BLANK:
									NeedCategory need = new NeedCategory();
									need.setId(id_pre);
									need.setIsParent(0);
									need.setDesignNo(num);
									needCategoryMapper
											.updateByPrimaryKeySelective(need);
									id_pre = 0;
									flag = false;
									break;

								default:
									break;
								}
							}
						}
					} else {
						for (int colIx = 4; colIx <= 11 && flag; colIx++) {

							if (colIx == 4) {
								HSSFCell cell = hssfRow.getCell(colIx);
								String cellString = "";

								switch (cell.getCellType()) {

								case Cell.CELL_TYPE_STRING:
									cellString = cell.getStringCellValue();

									num = cellString;
									break;
								default:
									break;
								}
							} else {

								HSSFCell cell = hssfRow.getCell(colIx);
								if (cell == null) {
									NeedCategory need = new NeedCategory();
									need.setId(id_pre);
									need.setIsParent(0);
									need.setDesignNo(num);
									needCategoryMapper
											.updateByPrimaryKeySelective(need);
									id_pre = 0;
									break;
								}
								String cellString = "";

								switch (cell.getCellType()) {

								case Cell.CELL_TYPE_STRING:
									cellString = cell.getStringCellValue()
											.trim();

									// 首先判断是否已经存过了，如果已经存过了，就返回存过的单元格的id，如果没有存过，就返回的是-1
									NeedCategory near = getNearNeedCat(cellString);

									if (near == null) {
										NeedCategory need = new NeedCategory();
										need.setParentId(id_pre);
										need.setName(cellString);
										need.setStatus(1);
										need.setSortOrder(1);
										need.setIsParent(1);
										need.setCreated(new Date());
										need.setUpdated(new Date());
										need.setType(0);
										need.setIsPon(0);
										needCategoryMapper.insert(need);
										cell.setCellValue(need.getId() + "");
										id_pre = need.getId();
									} else {
										if (near.getParentId() == id_pre) {
											cell.setCellValue(near.getId() + "");
											id_pre = near.getId();
										} else {
											NeedCategory need = new NeedCategory();
											need.setParentId(id_pre);
											need.setName(cellString);
											need.setStatus(1);
											need.setSortOrder(1);
											need.setIsParent(1);
											need.setCreated(new Date());
											need.setUpdated(new Date());
											need.setType(0);
											need.setIsPon(0);
											needCategoryMapper.insert(need);
											cell.setCellValue(need.getId() + "");
											id_pre = need.getId();
										}

									}
									break;
								case Cell.CELL_TYPE_BLANK:
									NeedCategory need = new NeedCategory();
									need.setId(id_pre);
									need.setIsParent(0);
									need.setDesignNo(num);
									needCategoryMapper
											.updateByPrimaryKeySelective(need);
									id_pre = 0;
									flag = false;
									break;

								default:
									break;
								}
							}
						}
					}
				}
			}

			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				System.out.println("number of rows:" + rowNum);
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);

				DesignNeed need = new DesignNeed();

				HSSFCell cell0 = hssfRow.getCell(0);

				switch (cell0.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					need.setId(Integer.parseInt(cell0.getStringCellValue()));
					break;
				case Cell.CELL_TYPE_NUMERIC:
					need.setId((int) cell0.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_BLANK:

					break;
				default:
					break;
				}

				HSSFCell cell1 = hssfRow.getCell(1);
				if (cell1 == null) {

				} else {
					switch (cell1.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setUserDemand(cell1.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}

				}

				HSSFCell cell2 = hssfRow.getCell(2);
				if (cell2 == null) {

				} else {
					switch (cell2.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPpr(cell2.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}

				}

				HSSFCell cell3 = hssfRow.getCell(3);
				if (cell3 == null) {

				} else {

					switch (cell3.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPdtDegree(cell3.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell4 = hssfRow.getCell(4);
				if (cell4 == null) {

				} else {

					switch (cell4.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setDesignNo(cell4.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell5 = hssfRow.getCell(5);
				if (cell5 == null) {

				} else {
					switch (cell5.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						Integer id = Integer.parseInt(cell5
								.getStringCellValue());
						if (id == 303) {
							System.out.println(id);
						}
						String name = needCategoryMapper.selectByPrimaryKey(id)
								.getName();
						if (name.equals("PON功能库")) {
							need.setIsPon(1);
						} else {
							need.setIsPon(0);
						}

						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell6 = hssfRow.getCell(6);
				if (cell6 == null) {

				} else {

					switch (cell6.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setNeedClass1(Integer.parseInt(cell6
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setNeedClass1((int) cell6.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell7 = hssfRow.getCell(7);
				if (cell7 == null) {

				} else {

					switch (cell7.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setNeedClass2(Integer.parseInt(cell7
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setNeedClass2((int) cell7.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell8 = hssfRow.getCell(8);
				if (cell8 == null) {

				} else {

					switch (cell8.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setNeedClass3(Integer.parseInt(cell8
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setNeedClass3((int) cell8.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell9 = hssfRow.getCell(9);
				if (cell9 == null) {

				} else {

					switch (cell9.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setNeedClass4(Integer.parseInt(cell9
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setNeedClass4((int) cell8.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell10 = hssfRow.getCell(10);
				if (cell10 == null) {

				} else {

					switch (cell10.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setNeedClass5(Integer.parseInt(cell10
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setNeedClass5((int) cell10.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell12 = hssfRow.getCell(12);
				if (cell12 == null) {

				} else {

					switch (cell12.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setDrs(cell12.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setDrs(((int) cell12.getNumericCellValue()) + "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell13 = hssfRow.getCell(13);
				if (cell13 == null) {

				} else {

					switch (cell13.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setIsRequired(cell13.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setIsRequired(((int) cell13.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell14 = hssfRow.getCell(14);
				if (cell14 == null) {

				} else {

					switch (cell14.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setOltMsanPro(cell14.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setOltMsanPro(((int) cell14.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell15 = hssfRow.getCell(15);
				if (cell15 == null) {

				} else {

					switch (cell15.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setOnuMsanPro(cell15.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setOnuMsanPro(((int) cell15.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell16 = hssfRow.getCell(16);
				if (cell16 == null) {

				} else {

					switch (cell16.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPrChoose(cell16.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setPrChoose(((int) cell16.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell17 = hssfRow.getCell(17);
				if (cell17 == null) {

				} else {

					switch (cell17.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPrIstest(cell17.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setPrIstest(((int) cell17.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell18 = hssfRow.getCell(18);
				if (cell18 == null) {

				} else {

					switch (cell18.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPrSpecification(cell18.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setPrSpecification(((int) cell18
								.getNumericCellValue()) + "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell19 = hssfRow.getCell(19);
				if (cell19 == null) {

				} else {

					switch (cell19.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPrFunction(cell19.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setPrFunction(((int) cell19.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell20 = hssfRow.getCell(20);
				if (cell20 == null) {

				} else {

					switch (cell20.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setNeedDegree(cell20.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setNeedDegree(((int) cell20.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell21 = hssfRow.getCell(21);
				if (cell21 == null) {

				} else {

					switch (cell21.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setImpVersion(cell21.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setImpVersion(((int) cell21.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell22 = hssfRow.getCell(22);
				if (cell22 == null) {

				} else {

					switch (cell22.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setScIsimp(cell22.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setScIsimp(((int) cell22.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell23 = hssfRow.getCell(23);
				if (cell23 == null) {

				} else {

					switch (cell23.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setScRemarks(cell23.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setScRemarks(((int) cell23.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell24 = hssfRow.getCell(24);
				if (cell24 == null) {

				} else {

					switch (cell24.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setStIsimp(cell24.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setStIsimp(((int) cell24.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell25 = hssfRow.getCell(25);
				if (cell25 == null) {

				} else {

					switch (cell25.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setStRamarks(cell25.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setStRamarks(((int) cell25.getNumericCellValue())
								+ "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell26 = hssfRow.getCell(26);
				if (cell26 == null) {

				} else {

					switch (cell26.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaMdu(cell26.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setPaMdu(((int) cell26.getNumericCellValue()) + "");
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell27 = hssfRow.getCell(27);
				if (cell27 == null) {

				} else {

					switch (cell27.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaDiskB(cell27.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell28 = hssfRow.getCell(28);
				if (cell28 == null) {

				} else {

					switch (cell28.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaDiskC(cell28.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell29 = hssfRow.getCell(29);
				if (cell29 == null) {

				} else {

					switch (cell29.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaDiskD(cell29.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell30 = hssfRow.getCell(30);
				if (cell30 == null) {

				} else {

					switch (cell30.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaDiskE(cell30.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell31 = hssfRow.getCell(31);
				if (cell31 == null) {

				} else {

					switch (cell31.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaBackplane(cell31.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell32 = hssfRow.getCell(32);
				if (cell32 == null) {

				} else {

					switch (cell32.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaRemotescope(cell32.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell33 = hssfRow.getCell(33);
				if (cell33 == null) {

				} else {

					switch (cell33.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaStructure(cell33.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell34 = hssfRow.getCell(34);
				if (cell34 == null) {

				} else {

					switch (cell34.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaCable(cell34.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell35 = hssfRow.getCell(35);
				if (cell35 == null) {

				} else {

					switch (cell35.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaPowerPanel(cell35.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell36 = hssfRow.getCell(36);
				if (cell36 == null) {

				} else {

					switch (cell36.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaFanControlUnit(cell36.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell37 = hssfRow.getCell(37);
				if (cell37 == null) {

				} else {

					switch (cell37.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaMmcp(cell37.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell38 = hssfRow.getCell(38);
				if (cell38 == null) {

				} else {

					switch (cell38.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaOfficialDisk(cell38.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell39 = hssfRow.getCell(39);
				if (cell39 == null) {

				} else {

					switch (cell39.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaTechnicalManual(cell39.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}

				HSSFCell cell40 = hssfRow.getCell(40);
				if (cell40 == null) {

				} else {

					switch (cell40.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaInstallationManual(cell40
								.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell41 = hssfRow.getCell(41);
				if (cell41 == null) {

				} else {

					switch (cell41.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaNetManual(cell41.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell42 = hssfRow.getCell(42);
				if (cell42 == null) {

				} else {

					switch (cell42.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaUseManual(cell42.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell43 = hssfRow.getCell(43);
				if (cell43 == null) {

				} else {

					switch (cell43.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setPaUserManual(cell43.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell44 = hssfRow.getCell(44);
				if (cell44 == null) {

				} else {

					switch (cell44.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setRdTriggerCondition(cell44.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell45 = hssfRow.getCell(45);
				if (cell45 == null) {

				} else {

					switch (cell45.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setRdInput(cell45.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell46 = hssfRow.getCell(46);
				if (cell46 == null) {

				} else {

					switch (cell46.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setRdHandle(cell46.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell47 = hssfRow.getCell(47);
				if (cell47 == null) {

				} else {

					switch (cell47.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setRdOutput(cell47.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				HSSFCell cell48 = hssfRow.getCell(48);
				if (cell48 == null) {

				} else {

					switch (cell48.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setRdSpecialNeeds(cell48.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				need.setCreatedTime(new Date());
				need.setUpdatedTime(new Date());
				HSSFCell cell51 = hssfRow.getCell(51);
				if (cell51 == null) {

				} else {

					switch (cell51.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						need.setIsPon(Integer.parseInt(cell51
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						need.setIsPon((int) cell51.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:

						break;
					default:
						break;
					}
				}
				designNeedMapper.insert(need);
			}
			return PageResult.ok();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return PageResult.build(500, "读取表格数据失败");

	}

	@Transactional
	public PageResult readTestExcel(String path) {
		try {

			testCaseMapper.clear();
			testCategoryMapper.clear();

			InputStream is = new FileInputStream(path);
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
			for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
				System.out.println("number of sheet:" + numSheet);
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				if (hssfSheet == null) {
					continue;
				}

				for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
					System.out.println("number of rows:" + rowNum);
					HSSFRow hssfRow = hssfSheet.getRow(rowNum);
					// 开始读取行
					boolean flag = true;
					int id_pre = 0;
					String num = "";
					for (int colIx = 1; colIx <= 7 && flag; colIx++) {
						if (hssfRow.getCell(colIx) == null
								|| (hssfRow.getCell(colIx).getCellType() == Cell.CELL_TYPE_BLANK)) {
							continue;
						}
						if (colIx == 1) {
							HSSFCell cell = hssfRow.getCell(colIx);
							String cellString = "";

							switch (cell.getCellType()) {

							case Cell.CELL_TYPE_STRING:
								cellString = cell.getStringCellValue();

								num = cellString;
								break;
							case Cell.CELL_TYPE_NUMERIC:
								cellString = cell.getNumericCellValue() + "";

								num = "T"
										+ cellString.substring(0,
												cellString.length() - 2);
								break;
							default:
								break;
							}
						} else {
							HSSFCell cell = hssfRow.getCell(colIx);
							if (cell == null) {
								TestCategory test = new TestCategory();
								test.setId(id_pre);
								test.setIsParent(0);
								test.setCaseNo(num);
								testCategoryMapper
										.updateByPrimaryKeySelective(test);
								id_pre = 0;
								break;
							}
							String cellString = "";

							switch (cell.getCellType()) {

							case Cell.CELL_TYPE_STRING:
								cellString = cell.getStringCellValue().trim();

								if (!cellString.equals("a")) {
									// 首先判断是否已经存过了，如果已经存过了，就返回存过的单元格的id，如果没有存过，就返回的是-1
									TestCategory near = getNearTestCat(cellString);
									if (near == null) {
										TestCategory test = new TestCategory();
										test.setParentId(id_pre);
										test.setName(cellString);
										test.setIsParent(1);
										test.setStatus(1);
										test.setSortOrder(1);
										test.setCreated(new Date());
										test.setUpdated(new Date());
										test.setType(0);
										testCategoryMapper.insert(test);
										cell.setCellValue(test.getId() + "");
										id_pre = test.getId();
									} else {
										if (near.getParentId() == id_pre) {
											cell.setCellValue(near.getId() + "");
											id_pre = near.getId();
										} else if (near.getId() == id_pre) {
											TestCategory test = new TestCategory();
											test.setId(id_pre);
											test.setIsParent(0);
											testCategoryMapper
													.updateByPrimaryKeySelective(test);
											id_pre = 0;
											flag = false;
										} else {
											TestCategory test = new TestCategory();
											test.setParentId(id_pre);
											test.setName(cellString);
											test.setIsParent(1);
											test.setStatus(1);
											test.setSortOrder(1);
											test.setCreated(new Date());
											test.setUpdated(new Date());
											test.setType(0);
											testCategoryMapper.insert(test);
											cell.setCellValue(test.getId() + "");
											id_pre = test.getId();
										}

									}
									break;

								} else {
									TestCategory test = new TestCategory();
									test.setId(id_pre);
									test.setIsParent(0);
									test.setCaseNo(num);
									testCategoryMapper
											.updateByPrimaryKeySelective(test);
									id_pre = 0;
									flag = false;
									break;
								}
							case Cell.CELL_TYPE_BLANK:
								TestCategory test = new TestCategory();
								test.setId(id_pre);
								test.setIsParent(0);
								test.setCaseNo(num);
								testCategoryMapper
										.updateByPrimaryKeySelective(test);
								id_pre = 0;
								flag = false;
								break;

							default:
								break;
							}
						}
					}
				}
			}

			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);

			for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
				System.out.println("number of rows:" + rowNum);
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);

				TestCase test = new TestCase();

				HSSFCell cell0 = hssfRow.getCell(0);
				switch (cell0.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					test.setId(Integer.parseInt(cell0.getStringCellValue()));
					break;
				case Cell.CELL_TYPE_NUMERIC:
					test.setId((int) cell0.getNumericCellValue());
					break;
				case Cell.CELL_TYPE_BLANK:

					break;
				default:
					break;
				}

				HSSFCell cell1 = hssfRow.getCell(1);
				switch (cell1.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					test.setCaseNo(cell1.getStringCellValue());
					break;
				case Cell.CELL_TYPE_NUMERIC:
					break;
				case Cell.CELL_TYPE_BLANK:
					break;
				default:
					break;
				}

				HSSFCell cell2 = hssfRow.getCell(2);
				if (cell2 == null) {

				} else {
					switch (cell2.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setTestClass1(Integer.parseInt(cell2
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						test.setTestClass1((int) cell2.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}

				}

				HSSFCell cell3 = hssfRow.getCell(3);
				if (cell3 == null) {

				} else {

					switch (cell3.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setTestClass2(Integer.parseInt(cell3
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						test.setTestClass2((int) cell3.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				HSSFCell cell4 = hssfRow.getCell(4);
				if (cell4 == null) {

				} else {

					switch (cell4.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setTestClass3(Integer.parseInt(cell4
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						test.setTestClass3((int) cell4.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				HSSFCell cell5 = hssfRow.getCell(5);
				if (cell5 == null) {

				} else {

					switch (cell5.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setTestClass4(Integer.parseInt(cell5
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						test.setTestClass4((int) cell5.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				HSSFCell cell6 = hssfRow.getCell(6);
				if (cell6 == null) {

				} else {

					switch (cell6.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setPonCase(Integer.parseInt(cell6
								.getStringCellValue()));
						break;
					case Cell.CELL_TYPE_NUMERIC:
						test.setPonCase((int) cell6.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				HSSFCell cell8 = hssfRow.getCell(8);
				if (cell8 == null) {

				} else {

					switch (cell8.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setAutoUseCase(cell8.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				HSSFCell cell9 = hssfRow.getCell(9);
				if (cell9 == null) {

				} else {

					switch (cell9.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setFma(cell9.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				HSSFCell cell10 = hssfRow.getCell(10);
				if (cell10 == null) {

				} else {

					switch (cell10.getCellType()) {
					case Cell.CELL_TYPE_STRING:
						test.setAutoStatus(cell10.getStringCellValue());
						break;
					case Cell.CELL_TYPE_NUMERIC:
						break;
					case Cell.CELL_TYPE_BLANK:
						break;
					default:
						break;
					}
				}

				test.setCreatedTime(new Date());
				test.setUpdatedTime(new Date());

				testCaseMapper.insert(test);

			}
			return PageResult.ok();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return PageResult.build(500, "读取表格数据失败");
	}

	/**
	 * @Description 根据name获取最近的(id最大的)NeedCategory节点
	 * @Date 2017年8月11日
	 * @param name
	 * @return
	 */
	public NeedCategory getNearNeedCat(String name) {
		NeedCategoryExample example = new NeedCategoryExample();
		NeedCategoryExample.Criteria criteria = example.createCriteria();
		criteria.andNameEqualTo(name);
		List<NeedCategory> result = needCategoryMapper.selectByExample(example);
		if (result.size() == 0) {
			return null;
		}
		Collections.sort(result, new Comparator<NeedCategory>() {

			@Override
			public int compare(NeedCategory o1, NeedCategory o2) {
				// TODO Auto-generated method stub
				return o2.getId().compareTo(o1.getId());
			}

		});
		return result.get(0);
	}

	public TestCategory getNearTestCat(String name) {

		List<TestCategory> result = testCategoryMapper.selectByName(name);
		if (result.size() == 0) {
			return null;
		}
		Collections.sort(result, new Comparator<TestCategory>() {

			@Override
			public int compare(TestCategory o1, TestCategory o2) {
				// TODO Auto-generated method stub
				return o2.getId().compareTo(o1.getId());
			}

		});
		return result.get(0);
	}
}
